﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;

namespace Core.Dao
{
    class TicketCategoryDao
    {
        public bool insert(TicketCategory city)
        {
            object[,] param = {
                {"@Name", city.Name},
                {"@Desc", city.Desc},
            };
            String sql = "insert into TICKET_CATEGORIES(TKC_NAME, TCK_DESC) " +
                "values(@Name, @Desc)";
            return DB.exec(sql, param);
        }

        public bool update(TicketCategory city)
        {
            object[,] param = {
                {"@Name", city.Name},
                {"@Desc", city.Desc},
                {"@id", city.Id},
            };
            String sql = "update TICKET_CATEGORIES set TKC_NAME = @Name, TCK_DESC = @Desc " +
                "where TKC_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from TICKET_CATEGORIES where TKC_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from TICKET_CATEGORIES where TKC_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from TICKET_CATEGORIES where TKC_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable getAll()
        {
            String sql = "select * from TICKET_CATEGORIES order by TKC_NAME";
            return DB.getData(sql);
        }

        public DataTable getAllByServiceTypeIdAndDepartmentId(int serviceTypeId, int departmentId)
        {
            object[,] param = {
                {"@serviceTypeId", serviceTypeId},
                {"@departmentId", departmentId}
            };
            String sql = "select distinct TKC.* from TICKET_CATEGORIES TKC, SERVICETYPES_TICKETCATEGORIES STC, DEPARTMENTS_TICKETCATEGORIES DTC " +
                "where TKC.TKC_ID = STC.TKC_ID and STC.SVT_ID = @serviceTypeId and TKC.TKC_ID = DTC.TKC_ID and DTC.DEP_ID = @departmentId " +
                "order by TKC.TKC_NAME";
            return DB.getData(sql, param);
        }
    }
}
